package com.wsj.dao.hibernate;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.wsj.dao.IHomeSiteNewsClassDao;
import com.wsj.web.vo.homeSite.BmClassType;
import com.wsj.web.vo.homeSite.HomeSiteAdBannerDescription;
import com.wsj.web.vo.homeSite.HomeSiteNewsClass;

public class HomeSiteNewsClassDaoImpl extends HibernateDaoSupport implements IHomeSiteNewsClassDao{

	private JdbcTemplate jdbcTemplate;
	
	public boolean addNewsClass(HomeSiteNewsClass hs) throws DataAccessException {
		this.getHibernateTemplate().save(hs);
		return true;
	}

	public boolean updateNewsClass(HomeSiteNewsClass hs) throws DataAccessException {
		this.getHibernateTemplate().saveOrUpdate(hs);
		return true;
	}
	
	
	public boolean deleteNewsClassByParentId(Long parentId) throws DataAccessException {
		String sql = "delete from homeSite_news_class where paren_id = " + parentId;
		this.jdbcTemplate.execute(sql);
		return true;
	}

	public HomeSiteNewsClass loadNewsClassById(Long hsId) throws DataAccessException {
		HomeSiteNewsClass hp = new HomeSiteNewsClass();
		Object obj = this.getHibernateTemplate().get(HomeSiteNewsClass.class, hsId);
		if(obj!=null){
			hp = (HomeSiteNewsClass)obj;
		}
		return hp;
	}
	
	public List loadNewsClassList(Long parentId,Integer typeId) throws DataAccessException {
		List list = new ArrayList();
		String sql = "";
		
		// 只根据 类别ID查询
		if(parentId!=null && !parentId.equals(new Long(0)) && typeId==null){
			sql = " from HomeSiteNewsClass  hs where 1 = 1 and  hs.parenId = ? ";
			list = this.getHibernateTemplate().find(sql,parentId);
		}
		// 只根据 父类ID查询
		if(typeId!=null&&!typeId.equals(0)&& parentId==null){
			sql = " from HomeSiteNewsClass  hs where 1 = 1 and hs.bmClassType.id = ? ";
			list = this.getHibernateTemplate().find(sql,typeId);
		}
		//  根据 父类ID、类别ID 查询所有
		if(parentId!=null && !parentId.equals(new Long(0)) && typeId!=null&&!typeId.equals(0)){
			sql = " from HomeSiteNewsClass  hs where 1 = 1 and  hs.parenId = ? and hs.bmClassType.id = ? ";
			list = this.getHibernateTemplate().find(sql,new Object[]{parentId,typeId});
		}
		return list;
	}
	
	public List loadListByClassTypeId(Integer classTypeId) throws DataAccessException {
		List list = new ArrayList();
		String sql = "from HomeSiteNewsClass h where h.bmClassType.id = ? ";
		list = this.getHibernateTemplate().find(sql,classTypeId);
		return list;
	}

	

	public List loadAllNewsClass() throws DataAccessException {
		List list = new ArrayList();
		String sql = "from HomeSiteNewsClass where 1 = 1 ";
		list = this.getHibernateTemplate().find(sql);
		return list;
	}


	
	public boolean deleteNewsClass(Long hsId) throws DataAccessException {
		
		String sql = "delete from homeSite_news_class where id = " + hsId;
		this.jdbcTemplate.execute(sql);
		
		/*HomeSiteNewsClass hp = this.loadNewsClassById(HomeSiteNewsClassId);
		if(hp!=null){
			this.getHibernateTemplate().delete(hp);
			return true;
		}*/
		
		return false;
	}
	
	
	/**
	 * 通过父类ID查询子类所有分类
	 * @return 子类 String
	 * **/
	
	public String getChildStr(List childList,Integer typeId,Integer branch,Integer selected) throws DataAccessException{
		/*Session session = this.getSession();*/
		StringBuffer str = new StringBuffer("");
		if(childList == null || childList.size()<1){
			return "";
		}else{
			try {
				for(int i=0;i<childList.size();i++){
					HomeSiteNewsClass hp = (HomeSiteNewsClass)childList.get(i);
					/*session.lock(hp, LockMode.READ);*/
					if(branch==0){
						str.append("<option value="+hp.getId()+" "+(selected==hp.getId().intValue()?"selected":"")+">" 
							+"【"+hp.getId()+"】"+hp.getNames() + "</option>");
					}else if(branch==1){
						str.append("<option value="+hp.getId()+" "+(selected==hp.getId().intValue()?"selected":"")
							+">&nbsp;&nbsp;&nbsp;|--" +"【"+hp.getId()+"】"+hp.getNames() + "</option>");
					}else{
						StringBuffer temp = new StringBuffer("");
						for(int j=2;j<=branch;j++){
							temp.append("&nbsp;&nbsp;&nbsp;|");
						}
						str.append("<option value="+hp.getId()+" "+(selected==hp.getId().intValue()?"selected":"")
							+">"+temp+"&nbsp;&nbsp;&nbsp;|--" +"【"+hp.getId()+"】"+hp.getNames() + "</option>");
					}
					List children = this.loadNewsClassList(hp.getId(),typeId);
					if(children.size()>0){
						branch++;   //树枝控制node 非常重要
						str.append(getChildStr(children,typeId,branch,selected));
						branch--;
					}
				}
			} catch (HibernateException e) {
				e.printStackTrace();
			}/*finally{
				session.close();
			}*/
		}
		return str.toString();
	}
	

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public boolean updateSeq(Long newsClassId, Long parId) throws DataAccessException {
		Session session = this.getSession();
		Connection con = session.connection();
		String sql = "{call proc_updateSeq(?,?)}";
		PreparedStatement pst;
		try {
			pst = con.prepareStatement(sql);
			pst.setLong(1,newsClassId);
			pst.setLong(2,parId);
			pst.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
			return false;
	}

	public HomeSiteNewsClass loadNewsClassByNames(String names, Integer typeId) throws DataAccessException {
		// TODO Auto-generated method stub
		
		HomeSiteNewsClass homeSiteNewsClass=null;
		List list = new ArrayList();
		String sql = "";
		if(names!=null && !names.equals("")&&typeId!=null&&!typeId.equals(0)){
			sql = " from HomeSiteNewsClass  hs where 1 = 1 and  hs.names = ? and hs.bmClassType.id = ? ";
			list = this.getHibernateTemplate().find(sql,new Object[]{names,typeId});
		}
		if(list!=null&&!list.isEmpty()){
			homeSiteNewsClass=(HomeSiteNewsClass) list.get(0);
			
		}
		return homeSiteNewsClass;
	}

	
	/******
	 * 通过名称检测栏目分类是否存在
	 */
	public HomeSiteNewsClass loadNewsClassByNames(String names) throws DataAccessException 
	{
		String sql = " from HomeSiteNewsClass  hs where 1 = 1 and  hs.names = ? ";
		List list = this.getHibernateTemplate().find(sql,names);
		if(list!=null  && !list.isEmpty())
		{
			return (HomeSiteNewsClass)list.get(0);
		}
		else
		{
			return null;
		}
		
	}

	
	/******
	 *  通过类别id(新闻/广告)获取第一级分类列表 
	 */
	public List loadFirstHomeSiteNewsClassByTypeDao(Integer typeId) throws DataAccessException 
	{
		String sql=" select id,paren_id,names,is_del,sequence,layer,seq,bm_class_type_id,description from homeSite_news_class as nc where nc.paren_id=-1 and nc.bm_class_type_id ="+typeId;
		
		
		final List list = new ArrayList();
		
		 this.getJdbcTemplate().query(sql.toString(),new ResultSetExtractor(){
	          
	          public Object extractData(ResultSet rs) throws SQLException,DataAccessException 
	          {
	             while(rs.next())
	             {
	            	 HomeSiteNewsClass homeSiteNewsClass  =new HomeSiteNewsClass();  
	            	 homeSiteNewsClass.setId(rs.getLong("id"));
	            	 homeSiteNewsClass.setParenId(rs.getLong("paren_id"));
	            	 BmClassType bmClssType = new BmClassType();
	            	 bmClssType.setId(rs.getInt("bm_class_type_id"));
	            	 homeSiteNewsClass.setBmClassType(bmClssType);
	            	 homeSiteNewsClass.setNames(rs.getString("names"));
	            	 homeSiteNewsClass.setIsDel(rs.getShort("is_del"));
	            	 homeSiteNewsClass.setSequence(rs.getShort("sequence"));
	            	 homeSiteNewsClass.setLayer(rs.getShort("layer"));
	            	 homeSiteNewsClass.setDescription(rs.getString("description"));
	            	 homeSiteNewsClass.setSeq(rs.getString("seq"));
	            	 
	                 list.add(homeSiteNewsClass);
	             }
	             return list;
	          }
	      });
	        
		 return list;
	      
	}

	/*****
	 * 通过分类id和类别id(新闻/广告) ,获取该分类下面的子类
	 * 当前类是广告类别,或者该类下面的子分类为广告类型的分类列表
	 */
	public Map loadSecondHomeSiteNewsClassByTypeDao(Integer typeId, Long parenId) throws DataAccessException 
	{
		 String sql="select id,paren_id,names,is_del,sequence,layer,seq,bm_class_type_id,description from homeSite_news_class nc where"+ 
          "  nc.paren_id = "+parenId+" and nc.bm_class_type_id = "+typeId;
		 
			final Map<Long, String> map = new HashMap<Long, String>();
			 this.getJdbcTemplate().query(sql.toString(),new ResultSetExtractor()
			 {
		          public Object extractData(ResultSet rs) throws SQLException,DataAccessException 
		          {
		             while(rs.next())
		             {
		            	 HomeSiteNewsClass homeSiteNewsClass  =new HomeSiteNewsClass();  
		            	 homeSiteNewsClass.setId(rs.getLong("id"));
		            	 homeSiteNewsClass.setParenId(rs.getLong("paren_id"));
		            	 BmClassType bmClssType = new BmClassType();
		            	 bmClssType.setId(rs.getInt("bm_class_type_id"));
		            	 homeSiteNewsClass.setBmClassType(bmClssType);
		            	 homeSiteNewsClass.setNames(rs.getString("names"));
		            	 homeSiteNewsClass.setIsDel(rs.getShort("is_del"));
		            	 homeSiteNewsClass.setSequence(rs.getShort("sequence"));
		            	 homeSiteNewsClass.setLayer(rs.getShort("layer"));
		            	 homeSiteNewsClass.setDescription(rs.getString("description"));
		            	 homeSiteNewsClass.setSeq(rs.getString("seq"));
		            	 
		                 map.put(rs.getLong("id"), rs.getString("names"));
		                 
		             }
		             return map;
		          }
		      });
			 
			 return map;
	}

	/******
	 * 通过广告分类id,获取广告描述列表
	 */
	public Map loadAdBannerListByHomeSiteNewsClassIdDao(Long classId) throws DataAccessException 
	{
		String hql="from HomeSiteAdBannerDescription as d where d.homeSiteNewsClass.id =? ";
		List li = this.getHibernateTemplate().find(hql,classId);
		Map<Integer , String> ret=new HashMap<Integer , String>();
		if(li!=null  && !li.isEmpty())
		{
			for(int j=0;j<li.size();j++)
			{
				HomeSiteAdBannerDescription  de=(HomeSiteAdBannerDescription)li.get(j);
				ret.put(de.getId(), de.getPicName());
			}
		}
		return ret;
	}

	/*****
	 * 通过父分类id,资讯类型id,加载前几个分类
	 */
	public List<HomeSiteNewsClass> loadSCHQNewsClassListDao(Long classId, Integer classTypeId, Integer top) 
	{
		String hql="from HomeSiteNewsClass  as h where h.parenId=? and h.bmClassType.id=? ";
		Query query=this.getSession().createQuery(hql);
		query.setParameter(0, classId);
		query.setParameter(1, classTypeId);
		query.setMaxResults(top);
		return query.list();
	}
	

	
}
















